import os
import shutil

import pymysql

from app_config import BaseConfig
from db_linker import db
from models import Movies, Genres, MovieGenreLink
from run import app
import pandas as pd


def create_db_and_table():
    # 创建连接
    conn = pymysql.connect(host=BaseConfig.host,
                           port=BaseConfig.port,
                           user=BaseConfig.username,
                           password=BaseConfig.password)

    # 创建游标
    cursor = conn.cursor()

    # 创建数据库
    cursor.execute('SET FOREIGN_KEY_CHECKS = 0;')  # 关闭外键约束检查
    cursor.execute(
        f"CREATE DATABASE if not exists {BaseConfig.db} default character set utf8mb4 collate utf8mb4_general_ci")

    # 创建表
    db.create_all()

    print('初始化成功')


def handle_csv_to_db():
    """读取 csv数据到 db"""

    df = pd.read_csv('movie.csv')
    df.sort_values(by='mid', ascending=True, inplace=True)  # 按照 mid 升序
    df.fillna('', inplace=True)  # 填充空

    print(df.head())
    print(list(df))

    for index, row in df[:500][::-1].iterrows():
        name = row['name']  # 电影名

        # 判断是否存在
        if Movies.query.filter(
                Movies.name == name
        ).first():
            print(f'     {name} 已存在，忽略')
            continue

        # 不存在，添加
        directors = row['directors']
        actors = row['actors']
        genres = row['genres']
        desc = row['descri']
        year = row['shoot']
        mid = row['mid']

        print(name)
        print('     ', directors)
        print('     ', actors)
        print('     ', genres)
        print('     ', desc)
        print('     ', year)

        # 添加 电影
        db_movie = Movies(
            name=name,
            directors=directors,
            actors=actors,
            desc=desc,
            year=year,
            image_path=f'imgs/movies/{mid}/poster.jpg'
        )
        db.session.add(db_movie)
        db.session.flush()
        movie_id = db_movie.id

        # 解析类型
        for genres_item in genres.split('|'):
            genres_val = genres_item.strip()
            if genres_val:
                # 查询是否存在
                db_genre = Genres.query.filter(
                    Genres.value == genres_val
                ).first()

                if not db_genre:  # 不存在，添加
                    db_genre = Genres(value=genres_val)
                    db.session.add(db_genre)
                    db.session.flush()

                genre_id = db_genre.id  # 获取 类型id

                # 添加 关联
                db.session.add(
                    MovieGenreLink(
                        movie_id=movie_id,
                        genre_id=genre_id
                    )
                )

        # 提交 事务
        db.session.commit()


def clear_dir():
    for dir_name in os.listdir('static/imgs/movies'):
        print(dir_name)

        if not Movies.query.filter(
                Movies.image_path == f'imgs/movies/{dir_name}/poster.jpg'
        ).first():  # 不存在，删除文件夹
            dir_path = f'static/imgs/movies/{dir_name}'

            if os.path.exists(dir_path) and os.path.isdir(dir_path):
                shutil.rmtree(dir_path)
                print('     删除...')
        else:  # 存在，则只留下海报图
            del_path = f'static/imgs/movies/{dir_name}/picture.jpg'
            if os.path.exists(del_path):
                os.remove(del_path)


if __name__ == '__main__':
    """
    初始化 创建数据库，创建数据表（项目初始化运行一次即可。后续改动运行无用），解析csv电影数据
    """

    with app.app_context():
        create_db_and_table()

        # 解析 csv数据到 db
        handle_csv_to_db()

        # 清理多余的文件图片
        clear_dir()
